﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using VideoClassLibrary.Model;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class ClassRoomModule
    {

        /// <summary>
        /// 添加新教室
        /// </summary>
        public int AddNewRoom(V_room model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_room(");
            strSql.Append("Create_time,update_time,SchoolID,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished)");

            strSql.Append(" values (");
            strSql.Append("@Create_time,@update_time,@SchoolID,@VR_state,@VR_roomName,@VR_roomCode,@VR_cameraPN,@VR_cameraCode,@VR_cameraImgSize,@IsPublished)");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Create_time", DbType.DateTime, model.Create_time);
            db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.update_time);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "VR_state", DbType.Int32, model.VR_state);
            db.AddInParameter(dbCommand, "VR_roomName", DbType.AnsiString, model.VR_roomName);
            db.AddInParameter(dbCommand, "VR_roomCode", DbType.AnsiString, model.VR_roomCode);
            db.AddInParameter(dbCommand, "VR_cameraPN", DbType.AnsiString, model.VR_cameraPN);
            db.AddInParameter(dbCommand, "VR_cameraCode", DbType.AnsiString, model.VR_cameraCode);
            db.AddInParameter(dbCommand, "VR_cameraImgSize", DbType.AnsiString, model.VR_cameraImgSize);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }

        /// <summary>
        /// 添加学校教室
        /// </summary>
        /// <param name="model"></param>
        /// <param name="IPC">摄像头实体</param>
        /// <returns>add by zhangqian</returns>
        public int AddNewRoom(V_room model, IPCInfo_Table IPC,SVR_IPC_Relation svrIpcRelation)
        {
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Proc_NewClassRoom_Add");
            db.AddOutParameter(dbCommand, "ID", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "VR_state", DbType.Int32, model.VR_state);
            db.AddInParameter(dbCommand, "VR_roomName", DbType.AnsiString, model.VR_roomName);
            db.AddInParameter(dbCommand, "VR_roomCode", DbType.AnsiString, model.VR_roomCode);
            db.AddInParameter(dbCommand, "VR_cameraPN", DbType.AnsiString, model.VR_cameraPN);
            db.AddInParameter(dbCommand, "VR_cameraCode", DbType.AnsiString, model.VR_cameraCode);
            db.AddInParameter(dbCommand, "VR_cameraImgSize", DbType.AnsiString, model.VR_cameraImgSize);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            db.AddOutParameter(dbCommand, "IPCID", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "IPCName", DbType.AnsiString, IPC.IPCName);
            db.AddInParameter(dbCommand, "IPCIp", DbType.AnsiString, IPC.IPCIp);
            db.AddInParameter(dbCommand, "IPCPort", DbType.Int32, IPC.IPCPort);
            db.AddInParameter(dbCommand, "IPCUser", DbType.AnsiString, IPC.IPCUser);
            db.AddInParameter(dbCommand, "IPCPwd", DbType.AnsiString, IPC.IPCPwd);
            db.AddInParameter(dbCommand, "IPCStatus", DbType.Int32, IPC.IPCStatus);
            db.AddInParameter(dbCommand, "SVRID", DbType.Int32, svrIpcRelation.SVRID);
            db.AddInParameter(dbCommand, "RelationPipe", DbType.Int32, svrIpcRelation.RelationPipe);
            db.AddInParameter(dbCommand, "RelationComment", DbType.String, svrIpcRelation.RelationPipe);
            int roomId;
            try
            {
                db.ExecuteNonQuery(dbCommand);

                object obj = db.GetParameterValue(dbCommand, "ID");
                if (!int.TryParse(obj.ToString(), out roomId))
                {
                    return 0;
                }
            }
            catch
            {
                return 0;
            }
            return roomId;
        }

        /// <summary>
        /// 判断是否存在状态不为3的数据
        /// </summary>
        /// <param name="vsid"></param>
        /// <returns></returns>
        public  int ChkExsit(int vsid)
        {
            Database db = VDataBase.CreateDatabase();
            string sql = "SELECT count(*) FROM VideoFormatNeed_Table WHERE Status<>3 AND VSID=" + vsid;
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return Convert.ToInt32(db.ExecuteScalar(dbCommand));
        }


        /// <summary>
        /// 转video
        /// </summary>
        /// <param name="vsid"></param>
        /// <param name="appendix"></param>
        /// <returns></returns>
        public int VideoFormat(int vsid,string appendix)
        {

            Database db = VDataBase.CreateDatabase();
            string sql = "Insert into VideoFormatNeed_Table(VSID,Status,Appendix)Values({0},{1},'{2}')";
            sql = string.Format(sql, vsid, 4, appendix);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteNonQuery(dbCommand);
        }



        public  SVR_IPC_Relation GetByIPCID(int ipcID)
        {
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT * FROM SVR_IPC_Relation_Table WHERE IPCID="+ipcID);
            DataSet dataSet=db.ExecuteDataSet(dbCommand);
            if (dataSet.Tables[0].Rows.Count > 0)
            {
                var query = from s in dataSet.Tables[0].AsEnumerable()
                            select new SVR_IPC_Relation()
                                       {
                                           ID = s.Field<int>("ID"),
                                           SVRID = s.Field<int>("SVRID"),
                                           IPCID = s.Field<int>("IPCID"),
                                           RelationComment = s.Field<string>("RelationComment"),
                                           RelationPipe = s.Field<int>("RelationPipe")
                                       };
                return query.FirstOrDefault();
            }else
            {

                return null;
            }
        }
        public  DataSet   ChkSvrPort(string  svrPort,string  svrList)
        {
            Database db = VDataBase.CreateDatabase();
            string sql =
                @"SELECT * FROM SVR_IPC_Relation_Table sr  left join S_R_IPC_Relation_Table si on sr.ipcid=si.ipcid
                left join v_school s on si.sid=s.id
                left join v_room r on si.RID=r.ID  WHERE sr.SVRID=@SVRID  AND sr.RelationPipe=@RelationPipe";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@SVRID", DbType.Int32, svrList);
            db.AddInParameter(dbCommand, "@RelationPipe", DbType.Int32, svrPort);
            return db.ExecuteDataSet(dbCommand);
 
        }
        public DataSet ChkSvrPort(string svrPort, string svrList,string ipcID)
        {
            Database db = VDataBase.CreateDatabase();
            string sql =
                @"SELECT * FROM SVR_IPC_Relation_Table sr  left join S_R_IPC_Relation_Table si on sr.ipcid=si.ipcid
                left join v_school s on si.sid=s.id
                left join v_room r on si.RID=r.ID  WHERE sr.SVRID=@SVRID  AND sr.RelationPipe=@RelationPipe and sr.IPCID=@IPCID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@SVRID", DbType.Int32, svrList);
            db.AddInParameter(dbCommand, "@RelationPipe", DbType.Int32, svrPort);
            db.AddInParameter(dbCommand, "@IPCID", DbType.Int32, ipcID);
            return db.ExecuteDataSet(dbCommand);

        }

        /// <summary>
        /// 查找该学校下面的所有符合条件的教室
        /// </summary>
        public DataSet GetList(string strWhere,int schoolId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Create_time,update_time,SchoolID,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished");
            strSql.Append(" FROM V_room Where SchoolID=@schoolId where VR_roomName like'%" + strWhere + "% and IsPublished=1");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "schoolId", DbType.Int32, schoolId);
            //db.AddInParameter(dbCommand, "strWhere", DbType.AnsiString, strWhere);
            return db.ExecuteDataSet(dbCommand);
        }
        /// <summary>
        /// 根据学校和其他条件选教室信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetRoomListByWhereAndSID(string strWhere, int schoolId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Create_time,update_time,SchoolID,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished ");
            strSql.Append(" FROM V_room Where SchoolID=@schoolId");
            if (strWhere != "")
            {
                strSql.Append(strWhere);
            }
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "schoolId", DbType.Int32, schoolId);
            db.AddInParameter(dbCommand, "strWhere", DbType.AnsiString, strWhere);
            return db.ExecuteDataSet(dbCommand);
        }
        /// <summary>
        /// 课程列表
        /// </summary>
        /// <returns></returns>
        public DataSet QueryByAllCourseList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_courselist order by Create_time desc");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        /// <summary>
        /// 根据教室编号查询所找教室
        /// </summary>
        /// <param name="roomName">教室名称</param>
        /// <returns>返回教室名称表</returns>
        public DataSet GetRoomByroomCode(string roomName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,vr_roomcode from v_room where vr_roomName ="+roomName);
            strSql.Append("");
            if (roomName.Trim() != "")
            {

                strSql.Append(roomName);
            }
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());

        }
        /// <summary>
        /// 显示教室基本信息
        /// </summary>
        /// <returns></returns>
        public DataSet QueryRoomAll(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Create_time,update_time,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished from v_room where SchoolID=" + id + " and  IsPublished=1 order by Create_time desc");  
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text,strSql.ToString());
        }
        /// <summary>
        /// 无条件查找显示教室基本信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllRoom()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Create_time,update_time,SchoolID,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished from v_room  order by Create_time desc");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        /// <summary>
        /// 根据教室名查询教室
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet QueryByRoomname(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Create_time,update_time,SchoolID,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished from v_room where VR_roomName like '%" + name + "%'  and  IsPublished=1");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="userId"></param>
        /// <param name="roomId"></param>
        /// <returns></returns>
        public DataSet GetUserClassRoom(int schoolId,int userId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" select distinct( RoomID),VR_roomName from V_courselistDest a  join V_courselist  b  on  a.TrainClassID=b.trainClassID    ");
            sbSql.Append(" join v_room c on b.RoomID=c.ID ");
            sbSql.Append(" where  a.UserID= " + userId);
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, sbSql.ToString());

        }

        /// <summary>
        ///  根据ID获取一个教室实体
        /// </summary>
        /// <returns></returns>
        public V_room GetRoomModelById(int roomId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Create_time,update_time,SchoolID,VR_state,VR_roomName,VR_roomCode,VR_cameraPN,VR_cameraCode,VR_cameraImgSize,IsPublished from V_room  ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "ID", DbType.Int32, roomId);
            DataSet dsResult = db.ExecuteDataSet(command);
            V_room model = null;

            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                model = new V_room();
                model.Create_time = dr["Create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                model.ID = int.Parse(dr["ID"].ToString());
                model.SchoolID = int.Parse(dr["SchoolID"].ToString());
                model.VR_state = int.Parse(dr["VR_state"].ToString());
                model.VR_roomName = dr["VR_roomName"].ToString();
                model.VR_roomCode = dr["VR_roomCode"].ToString();
                model.VR_cameraPN = dr["VR_cameraPN"].ToString();
                model.VR_cameraCode = dr["VR_cameraCode"].ToString();
                model.VR_cameraImgSize = dr["VR_cameraImgSize"].ToString();
                dsResult = null;
            }
            return model;
        }

        /// <summary>
        /// 修改教室
        /// </summary>
        /// <returns></returns> 
        public int UpdateRoom(V_room model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_room set ");
            strSql.Append("Create_time=@Create_time,");
            strSql.Append("update_time=@update_time,");
            strSql.Append("SchoolID=@SchoolID,");
            strSql.Append("VR_state=@VR_state,");
            strSql.Append("VR_roomName=@VR_roomName,");
            strSql.Append("VR_roomCode=@VR_roomCode,");
            strSql.Append("VR_cameraPN=@VR_cameraPN,");
            strSql.Append("VR_cameraCode=@VR_cameraCode,");
            strSql.Append("VR_cameraImgSize=@VR_cameraImgSize,");
            strSql.Append("IsPublished=@IsPublished");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
            db.AddInParameter(dbCommand, "Create_time", DbType.DateTime, model.Create_time);
            db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.update_time);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "VR_state", DbType.Int32, model.VR_state);
            db.AddInParameter(dbCommand, "VR_roomName", DbType.AnsiString, model.VR_roomName);
            db.AddInParameter(dbCommand, "VR_roomCode", DbType.AnsiString, model.VR_roomCode);
            db.AddInParameter(dbCommand, "VR_cameraPN", DbType.AnsiString, model.VR_cameraPN);
            db.AddInParameter(dbCommand, "VR_cameraCode", DbType.AnsiString, model.VR_cameraCode);
            db.AddInParameter(dbCommand, "VR_cameraImgSize", DbType.AnsiString, model.VR_cameraImgSize);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            { return 1; }
            else
            { return -1; }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="infoTable"></param>
        /// <returns></returns>
        public int UpdateIPCRelationTable(SVR_IPC_Relation infoTable)
        {
            Database db = VDataBase.CreateDatabase();

            string sql = string.Empty;
            sql = "SELECT * FROM SVR_IPC_Relation_Table WHERE IPCID={0}";
            sql = string.Format(sql, infoTable.IPCID);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables[0].Rows.Count > 0)
            {
                sql =
                    "UPDATE SVR_IPC_Relation_Table SET SVRID='{0}',RelationPipe='{1}',RelationComment={2} where IPCID={3}";
                sql = string.Format(sql, infoTable.SVRID, infoTable.RelationPipe, infoTable.RelationComment,
                                    infoTable.IPCID
                    );
                 dbCommand = db.GetSqlStringCommand(sql);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
            {
                sql =
               "INSERT INTO SVR_IPC_Relation_Table(SVRID,RelationPipe,RelationComment,IPCID)VALUES({0},{1},'{2}',{3})";
                sql=string.Format(sql, infoTable.SVRID, infoTable.RelationPipe, infoTable.RelationComment, infoTable.IPCID);
                dbCommand = db.GetSqlStringCommand(sql);
                return db.ExecuteNonQuery(dbCommand);
            }
           
            return 0;
        }


        /// <summary>
        /// 修改教室状态
        /// </summary>
        /// <param name="schoolId">所属学校</param>
        /// <param name="IsPublished">状态</param>
        /// <param name="Nid">新闻id</param>
        /// <returns></returns>
        public DataSet UpdateIsPublished(int schoolId, int IsPublished, int Nid)
        {
            string sql = "update V_room set  IsPublished=@IsPublished where  ID=@rid and schoolid=@schoolId";
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(dbCommand, "rid", DbType.Int32, Nid);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, IsPublished);
            db.AddInParameter(dbCommand, "schoolid", DbType.Int32, schoolId);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds;
        }
		
		/// <summary>
		/// 删除一条数据
		/// </summary>
		public void RoomDelete(int RoomID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from V_room ");
			strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, RoomID);
            db.ExecuteNonQuery(dbCommand);

		}
    }
}
